package db;
import model.*;

import java.sql.*;
import java.util.ArrayList;

/**
 * DBEmployee.java
 * @author kbh
 * @version 5. oktober 2006, 2011.02.16
 *
 */
public class DBSupplier{
    private  Connection con;
    
    /** Creates a new instance of DBEmployee */
    public DBSupplier() {
      con = DBConnection.getInstance().getDBcon();
    }
    
  //Implements the methods from the interface
    // get all suppliers
    public ArrayList<Supplier> getAllSuppliers(boolean _retriveAssociation)
    {
        return miscWhere("", _retriveAssociation);
    }
    //get one supplier having the phone number
    public Supplier getSupplierPhoneNo(String _phoneno, boolean _retriveAssociation)
    {   String wClause = "  phoneNo = '" + _phoneno + "'";
        return singleWhere(wClause, _retriveAssociation);
    }
    //find one customerGroup having the id
    public Supplier getSupplierName(String _name, boolean _retriveAssociation)
    {
        String wClause = "name like '%" + _name + "%'";
        System.out.println("SearchSupplier " + wClause);
        return singleWhere(wClause, _retriveAssociation);
    }
  
     //insert a new customer group
    public int insertSupplier(Supplier _supplier) throws Exception
    {  
       int rc = -1;
	   String query="INSERT INTO supplier(name, address, country, phoneNo, email)  VALUES('"+
		    _supplier.getName()  + "','"  +
		    _supplier.getAddress()  + "','"  +
		    _supplier.getCountry() + "','" +
		    _supplier.getPhoneno() + "','" +
		    _supplier.getEmail() + "')";

       System.out.println("insert : " + query);
      try{ // insert new employee +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("Supplier ikke oprettet");
          throw new Exception ("Supplier is not inserted correct");
       }
       return(rc);
    }

	public int updateSupplier(Supplier _supplier)
	{
		Supplier supplier  = _supplier;
		int rc=-1;

		String query="UPDATE supplier SET "+
		 	  "name ='"+ supplier.getName()+"', "+
		 	  "address ='"+ supplier.getAddress() + "', " +
                          "country ='"+ supplier.getCountry() + "', " +
                          "phoneNo ='"+ supplier.getPhoneno() + "', " +
                          "email ='" + supplier.getEmail() + "' " +
		          " WHERE phoneNo = '"+ supplier.getPhoneno() + "'";
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in supplier db: "+ex);
	  	}
		return(rc);
	}
	
	public int deleteSupplier(String _phoneno)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM supplier WHERE phoneNo = '" +
				_phoneno + "'";
                System.out.println(query);
	  	try{ // delete from employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}// try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in supplier db: "+ex);
   	        }
		return(rc);
	}
	
	//private methods
    //michWere is used whenever we want to select more than one employee
	
	 
	private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Supplier> list = new ArrayList<Supplier>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	Supplier supplier = new Supplier();
		 supplier = buildSupplier(results);	
                 list.add(supplier);	
		}//end while
                 stmt.close();
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singelwhere is used when we only select one employee 	
	private Supplier singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Supplier supplier = new Supplier();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the customerGroup from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            supplier = buildSupplier(results);
                            stmt.close();
			}
                        else{ //no employee was found
                            supplier = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return supplier;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT name, address, country, phoneNo, email FROM supplier";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build a supplier object
	private Supplier buildSupplier(ResultSet results)
      {   Supplier supplier = new Supplier();
          try{ // the columns from the table emplayee  are used
        	    supplier.setName(results.getString("name"));
        	    supplier.setAddress(results.getString("address"));
        	    supplier.setCountry(results.getString("country"));
        	    supplier.setPhoneno(Integer.parseInt(results.getString("phoneNo")));
        	    supplier.setEmail(results.getString("email"));
          }
         catch(Exception e)
         {
             System.out.println("error in building the supplier object");
         }
         return supplier;
      }

 
}  
